package com.wang.dao;

import com.wang.pojo.HomeCost;
import com.wang.pojo.User;

import java.util.List;

public class HomeCostDao extends BaseDao {
    public int add(HomeCost homeCost) {
        String sql = "insert into home(user_id,costName,money,remark) values(?,?,?,?)";
        return update(sql, homeCost.getUser_id(), homeCost.getCostName(), homeCost.getMoney(), homeCost.getRemark());
    }

    public int delete(int id) {
        String sql = "delete from home where id = ?";
        return update(sql, id);
    }

    public int update(HomeCost homeCost) {
        String sql = "update home set costName = ?,money = ?,remark = ? where id = ?";
        return update(sql, homeCost.getCostName(), homeCost.getMoney(), homeCost.getRemark(), homeCost.getId());
    }

    public List<HomeCost> query(String keyword) {
        keyword = "%" + keyword + "%";
        String sql = "select home.id,costName,money,date,remark,name from home,user where costName like ? or money like ? or date like ? and home.user_id=user.id";
        return queryForList(HomeCost.class, sql, keyword, keyword, keyword);
    }

    public HomeCost getHomeCostById(int id) {
        String sql = "select home.id,costName,money,date,remark,name from home,user where home.id = ? and home.user_id=user.id";
        return queryForOne(HomeCost.class, sql, id);
    }

    public Integer queryForPageTotalCount(User user) {
        String sql = "select count(*) from home,(select * from user where id=? or privilege>?) user where user.id=user_id";
        Number count = (Number) queryForValue(sql, user.getId(), user.getPrivilege());
        return count.intValue();
    }

    public List<HomeCost> queryForPageItems(int begin, int pageSize, User user) {
        String sql = "select home.id,user.id user_id,costName,money,date,remark,name,sum(money)over(order by date)as sum " +
                "from home,(select * from user where id=? or privilege>?) user " +
                "where user.id=user_id order by date limit ?,?";
        return queryForList(HomeCost.class, sql, user.getId(), user.getPrivilege(), begin, pageSize);
    }




    public Integer queryForPageTotalCountByUserId(User user, int user_id) {
        String sql = "select count(*) from home,(select * from user where id=? or privilege>?) user where user.id=user_id and user_id =?";
        Number count = (Number) queryForValue(sql, user.getId(), user.getPrivilege(), user_id);
        return count.intValue();
    }

    public List<HomeCost> queryForPageItemsByUserId(int begin, int pageSize, User user, int user_id) {
        String sql = "select home.id,user.id user_id,costName,money,date,remark,name,sum(money)over(order by date)as sum " +
                "from home,(select * from user where id=? or privilege>?) user " +
                "where user.id=user_id and user_id = ? order by date limit ?,?";
        return queryForList(HomeCost.class, sql, user.getId(), user.getPrivilege(), user_id, begin, pageSize);
    }

    public Integer queryForPageTotalCountByKeyword(String keyword, User user) {
        keyword = "%" + keyword + "%";
        String sql = "select count(*)" +
                "from home,(select * from user where id=? or privilege>?) user " +
                "where user.id=user_id and (costName like ? or money like ? or date like ? or name like ?) ";
        Number count = (Number) queryForValue(sql, user.getId(), user.getPrivilege(),
                keyword, keyword, keyword, keyword);
        return count.intValue();
    }

    public List<HomeCost> queryForPageItemsByKeyword(int begin, int pageSize, String keyword, User user) {
        keyword = "%" + keyword + "%";
        String sql = "select home.id,costName,money,date,remark,name " +
                "from home,(select * from user where id=? or privilege>?) user " +
                "where user.id=user_id and (costName like ? or money like ? or date like ? or name like ?) " +
                "order by date limit ?,?";
        List<HomeCost> homeCosts = queryForList(HomeCost.class, sql, user.getId(), user.getPrivilege(),
                keyword, keyword, keyword, keyword, begin, pageSize);
        System.out.println(homeCosts);
        return homeCosts;
    }
}
